#!python2.7
# -*- coding: utf-8 -*-
#-----------------------------
# Note:
#           1. Header reference: http://www.useragentstring.com/pages/useragentstring.php
#-----------------------------

__author__ = 'Josh Hung'
__copyright__ = "Copyright 2015, The PSA Project"
__license__ = "Chain Sea Information Integration Co., Ltd."
__version__ = "0.0.1"
__maintainer__ = "Josh Hung"
__email__ = "josh.hung@chainsea.com.tw"
__status__ = "Development" ## Prototype, Development, Production

import os
import re
import sys
import json
import time
import random
import shutil
import string
import pymysql
import requests
from datetime import datetime, timedelta
from pymongo import MongoClient
reload(sys)
sys.setdefaultencoding('utf-8')
import gc 
gc.enable()


class crawler(object):
    """
    Common functions for PSA crawler.
    """
    def __init__(self):
        self.name = 'MyCrawlerModule'
        self.sleepBase = 5
        self.mysqlIP = '127.0.0.1'
        self.mysqlID = 'root'
        self.mysqlPWD = '123456'
        self.client = MongoClient('mongodb://%s:27017/' %(self.mysqlIP))
        self.cur = pymysql.connect(self.mysqlIP, self.mysqlID, self.mysqlPWD, 'crawler',
                                    autocommit=True,
                                    charset='utf8',
                                    use_unicode=True).cursor(pymysql.cursors.DictCursor)

        self.userAgentLt = self.getUserAgentLt()


    def getUserAgentLt(self):
        """
        Utilized in class __init__; get user agents in MySQL and return as a list of user agents.

        Returns:
            the list of user agents.
        """
        self.cur.execute('SELECT * FROM crawler.user_agent')
        userAgentLt = map(lambda i: i['user_agent'].encode('utf-8'), self.cur.fetchall())
        #print userAgentLt
        return userAgentLt

    def getBoardNumDict(self):
        """
        Utilized in class __init__; query in MySQL <dbName>.boardNumDict for numbers of board in website.

        Returns:
            the dictionary of board as key, and board number as value.
        """
        self.cur.execute('SELECT * FROM %s.boardNumDict;' %(self.dbName))
        data = self.cur.fetchall()
        boardNumDict = dict(map(lambda i: (i['board'].encode('utf-8'), i['num'].encode('utf-8')), data))
        return boardNumDict

    def getResponse(self, board, url):
        """
        Utilize in getPageContent(); sends request to url, writes log to MySQL log, try maximun 3 times, and return response or none if succeed or fail.

        Args:
            board (str): the board of the url.
            url (str): the desired url.
        Returns:
            the response of requests.get().
        """
        print 'Get: %s' %(url)
        j=1
        while j<=3:
            try:
                headers={'User-Agent': random.choice(self.userAgentLt)}
                #print headers
                a = datetime.now
                response = requests.get(url, verify=False, timeout=30, headers=headers)
                #print datetime.now-a
                self.cur.execute('INSERT INTO log (idx, board, url, url_status, create_time) \
                                VALUES ("%s", "%s", "%s", "http status code %s", "%s");' \
                                %(datetime.now().strftime('%Y%m%d_%H:%M:%S')+''.join([random.choice(string.ascii_uppercase) for i in range(0,4)]),
                                board,
                                url,
                                response.status_code,
                                datetime.now().strftime('%Y%m%d_%H:%M:%S.%f')))
                j=4
            except:
                print 'Error, retry: %i' %(j)
                self.cur.execute('INSERT INTO log (idx, board, url, url_status, create_time) \
                                VALUES ("%s", "%s", "%s", "connection error %s", "%s");' \
                                %(datetime.now().strftime('%Y%m%d_%H:%M:%S')+''.join([random.choice(string.ascii_uppercase) for i in range(0,4)]),
                                board,
                                url,
                                str(j),
                                datetime.now().strftime('%Y%m%d_%H:%M:%S.%f')))
                time.sleep(self.sleepBase**j)
                j+=1
                if j==4:
                    print 'Error, abort.'
                    self.cur.execute('INSERT INTO log (idx, board, url, url_status, create_time) \
                                    VALUES ("%s", "%s", "%s", "connection error %s", "%s");' \
                                    %(datetime.now().strftime('%Y%m%d_%H:%M:%S')+''.join([random.choice(string.ascii_uppercase) for i in range(0,4)]),
                                    board,
                                    url,
                                    'abort',
                                    datetime.now().strftime('%Y%m%d_%H:%M:%S.%f')))
                    response = None

        return response

    def regexIndex(self, url):
        """
        Utilize in writeHTMLToMongoDB(); truncate url for index in database or complete the url by domain url, usually overwrite in child class.

        Args:
            url (str): the complete url or partial url.
        Returns:
            the truncated url or completed url.
        """
        return url

    def writeHTMLToMongoDB(self, board, url, content):
        """
        Utilize in getPageContent(); query history url from <board>_html, insert or update data to MongoDB and write log to MySQL.

        Args:
            board (str): the board of the url.
            url (str): the url of the content.
            content (str): the raw html text, ex. response.content and response.text.
        """
        #if url in self.client[self.dbName]['%s_html' %(board)].distinct('url'):
        if url in [i['_id'].encode('utf-8') for i in self.client[self.dbName]['%s_html' %(board)].aggregate([{'$group':{'_id':"$url"}}])]:
            self.client[self.dbName]['%s_html' %(board)].update({'url': url},
                                                        {'$set': {'updateTime': datetime.now().strftime('%Y%m%d_%H:%M'),
                                                                    'html': content}}, upsert=True)
        else:
            self.client[self.dbName]['%s_html' %(board)].insert_one({'url': url,
                                                                'idx': self.regexIndex(url),
                                                                'board': board,
                                                                'createTime': datetime.now().strftime('%Y%m%d_%H:%M'),
                                                                'updateTime': '',
                                                                'html': content})

        self.cur.execute('INSERT INTO log (idx, board, url, url_status, create_time) \
                        VALUES ("%s", "%s", "%s", "%s", "%s");' \
                        %(datetime.now().strftime('%Y%m%d_%H:%M:%S')+''.join([random.choice(string.ascii_uppercase) for i in range(0,4)]),
                        board,
                        url,
                        'writeHTMLToMongoDB complete',
                        datetime.now().strftime('%Y%m%d_%H:%M:%S.%f')))

        self.client.close()

    def getParseContent(self, board, url, pageDict):
        """
        Utilize in getPageContent(); selects from history url and inserts or updates pageDict separately to MongoDB <board> and partial data to MySQL <board>_pageURL.

        Args:
            board (str): the board of the url, directing to corresponding collection and table of database.
            url (str): the url of pageDict, use as unique key in MongoDB and MySQL.
            pageDict (dict): the parsed data from raw html, returned by parseContent() of each cralwer class.
        """
        ## Write data to MongoDB <website> <board> collection.
        #if url in self.client[self.dbName]['%s' %(board)].distinct('url'):
        if url in [i['_id'].encode('utf-8') for i in self.client[self.dbName]['%s_html' %(board)].aggregate([{'$group':{'_id':"$url"}}])]:
            self.client[self.dbName]['%s' %(board)].update({'url': url},
                                                            {'$set': pageDict}, upsert=True)
        else:
            self.client[self.dbName]['%s' %(board)].insert_one(pageDict)

        ## Write data to MySQL <website> <board>_pageURL table.
        self.cur.execute('SELECT url FROM `%s_pageURL` ORDER BY cTimeObject DESC LIMIT 10000000000;' %(board))
        if url in map(lambda i: i['url'], self.cur.fetchall()):
            self.cur.execute('UPDATE `%s_pageURL` SET cTimeObject="%s", updateTime="%s", createTime="%s", messageNum="%s" \
                            WHERE idx="%s";' \
                            %(board, pageDict['cTimeObject'], pageDict['updateTime'], pageDict['createTime'], pageDict['messageNum'], pageDict['idx']))
        else:
            self.cur.execute('INSERT INTO `%s_pageURL` (idx, url, cTimeObject, updateTime, createTime, messageNum) \
                            VALUES ("%s", "%s", "%s", "%s", "%s", "%s");' \
                            %(board, pageDict['idx'], pageDict['url'], pageDict['cTimeObject'], pageDict['updateTime'], pageDict['createTime'], pageDict['messageNum']))

        ## Write log to <website> log table.
        self.cur.execute('INSERT INTO log (idx, board, url, url_status, create_time) \
                        VALUES ("%s", "%s", "%s", "%s", "%s");' \
                        %(datetime.now().strftime('%Y%m%d_%H:%M:%S')+''.join([random.choice(string.ascii_uppercase) for i in range(0,4)]),
                        board,
                        url,
                        'getPageContent complete',
                        datetime.now().strftime('%Y%m%d_%H:%M:%S.%f')))

        self.client.close() #

    def getPageContent(self, board, url):
        """
        Utilize getResponse(), write html text to MongoDB by writeHTMLToMongoDB(), parse content and write result by getParseContent().

        Args:
            board (str): the board of the url.
            url (str): the desired url.
        """
        response = self.getResponse(board, url)
        #print response.content
        if (response!=None) and (response.ok): ##response!=None to avoid error url, response.ok to avoid something like HTTP 404
            content = response.content
            self.writeHTMLToMongoDB(board, url, content)

            htmlDocument = [i for i in self.client[self.dbName]['%s_html' %(board)].find({'url': url})][0]
            #print htmlDocument
            pageDict = self.parseContent(htmlDocument)

            pageDict['author'] = self.author_ETL(pageDict['author'])
            if datetime.now()<datetime.strptime(pageDict['cTimeObject'],'%Y%m%d_%H:%M'):
                pageDict['cTimeObject'] = datetime.strptime(datetime.now().strftime('%Y%m%d')+'_'+pageDict['cTime'],'%Y%m%d_%H:%M').strftime('%Y%m%d_%H:%M')
                pageDict['cDate'] = datetime.now().strftime('%Y%m%d')

            map(lambda key: pageDict.update({key: htmlDocument[key]}), ['idx', 'createTime', 'updateTime'])
            self.getParseContent(board, url, pageDict)

    def writeJsonDict(self, dbName, collectionName, startDate, endDate, fixedBoard=False):
        """
        Utilize in run(); Query in MongoDB on createTime and updateTime, and wrap data into my original json file format.

        Args:
            dbName (str): the database of desired query.
            collectionName (str): the collection of desired query.
            startDate (str): the starting date of the expected result, format "YYYYMMDD".
            endDate (str): the ending date of the expected result, format "YYYYMMDD".
        """
        createData = self.client[dbName][collectionName].find({'createTime':{'$gte':startDate+'_00:00', '$lte':endDate+'_24:00'}}).sort('cDate', -1)
        updateData = self.client[dbName][collectionName].find({'updateTime':{'$gte':startDate+'_00:00', '$lte':endDate+'_24:00'}}).sort('cDate', -1)
        actualData = self.client[dbName][collectionName].find({'cDate':{'$gte':startDate, '$lte':endDate}}).sort('cDate', -1) #
        dataLt = [i for i in createData] + [i for i in updateData] + [i for i in actualData]

        if len(dataLt)==0:
            print 'Retrieve no data: %s %s (%s-%s)' %(dbName, collectionName, startDate, endDate)

        if fixedBoard==False:
            jsonDict={}
            removeKeys = ['_id', 'createTime', 'updateTime', 'idx']
            for i in dataLt:
                pageDict = dict((key,value) for (key,value) in i.iteritems() if not key in removeKeys)
                jsonDict[i['idx']] = pageDict

            jsonFileName = '[%s-%s]_%s_%s.json' %(startDate, endDate, dbName, collectionName)
            jsonFileName = jsonFileName.replace('/','')
            with open('./0/ETL/jsonFiles/'+jsonFileName, 'w') as j:
                json.dump(jsonDict, j, ensure_ascii=False, encoding='utf8')
        else:
            jsonDict={}
            removeKeys = ['_id', 'createTime', 'updateTime', 'idx']
            for i in dataLt:
                pageDict = dict((key,value) for (key,value) in i.iteritems() if not key in removeKeys)
                pageDict['sourceBoard'] = dbName
                jsonDict[i['idx']] = pageDict

            jsonFileName = '[%s-%s]_%s_%s_fixedBoard.json' %(startDate, endDate, dbName, collectionName)
            jsonFileName = jsonFileName.replace('/','')
            with open('./0/ETL/jsonFiles/'+jsonFileName, 'w') as j:
                json.dump(jsonDict, j, ensure_ascii=False, encoding='utf8')

    def run(self, execClass, board, fixedBoard=False):
        """
        Get boardURLLt by getBoardURLLt(), and for loop over each url, if excepted, write *.txt and "error" in MySQL.

        Args:
            execClass (class): the desired execution class.
            board (str): the desired board of the website.
        """
        url = ''
        cur = execClass.cur
        cur.execute('SELECT * FROM crawler.crawler_index WHERE dbName="%s" AND board_en="%s";' %(execClass.dbName, board))
        crawlerInfo = cur.fetchall()
        crawler_id = str(crawlerInfo[0]['crawler_id']).zfill(6)

        try:
            cur.execute('CALL crawler.startCrawling(%s);' %(crawler_id))

            boardURLLt, new_url_num, update_url_num = execClass.getBoardURLLt(board)
            print 'new_url_num: %i' %(new_url_num)
            print 'update_url_num: %i' %(update_url_num)
            cur.execute('UPDATE crawler.crawler_index SET new_url_num="%i", update_url_num="%i" WHERE crawler_id="%s";' \
                        %(new_url_num, update_url_num, crawler_id))
            random.shuffle(boardURLLt)
            boardURLLt = [i for i in boardURLLt if i != []]
            for url in boardURLLt:
                execClass.getPageContent(board, url)
                time.sleep(random.random()*self.sleepBase)

            self.writeJsonDict(execClass.dbName, board, (datetime.now()+timedelta(days=-1)).strftime('%Y%m%d'), datetime.now().strftime('%Y%m%d'), fixedBoard)

            cur.execute('CALL crawler.finishCrawling(%s);' %(crawler_id))
        except Exception, e:
            self.writeJsonDict(execClass.dbName, board, (datetime.now()+timedelta(days=-1)).strftime('%Y%m%d'), datetime.now().strftime('%Y%m%d'), fixedBoard)
            print e
            cur.execute('UPDATE crawler.crawler_index SET crawler_status="error", error_msg="%s" WHERE crawler_id="%s";' \
                        %(e.args[0], crawler_id))
            url = ', '.join(url) if isinstance(url, list) else url
            with open ('%s.txt' \
                        %(datetime.now().strftime('%Y%m%d_%H%M%S')+''.join([random.choice(string.ascii_uppercase) for i in range(0,4)])),
                        'a') as f:
            
                f.write(datetime.now().strftime('%Y%m%d_%H:%M:%S')+'\n')
                f.write(execClass.dbName+'\n')
                f.write(board+'\n')
                f.write(url+'\n')
                f.write('Error Message:\n')
                f.write(str(e)+'\n')
                f.close()
        cur.close()
        del cur

    def getPageURLURL(self, board):
        """
        Utilize in getBoardURLLt(); get queueURLLt and historyURLLt according to subclass and the given board, query urls from <board>_queue and history not been updated from MySQL.

        Args:
            board (str): the desired table name of MySQL subclass database.
        Returns:
            queueURLLt form <board>_queue and historyURLLt from <board>_pageURL sorting by mysql_create_time.
        """
        ## queue url
        self.cur.execute('SELECT url FROM `%s_queue`;' %(board))
        queueURLLt = map(lambda i: i['url'].encode('utf-8'), self.cur.fetchall())
        self.cur.execute('TRUNCATE `%s_queue`;' %(board))

        ## history url
        self.cur.execute('SELECT * FROM `%(board)s_pageURL` WHERE `%(board)s_pageURL`.url NOT IN (SELECT url FROM `%(board)s_queue`) AND mysql_update_time="0000-00-00 00:00:00" ORDER BY mysql_create_time DESC LIMIT %(historyNum)i;' \
                        %({'board': board, 'historyNum': self.historyNum}))
        historyURLLt = map(lambda i: i['url'].encode('utf-8'), self.cur.fetchall())

        return queueURLLt, historyURLLt


    def author_ETL(self, author):
         spname = [u'汐止',u'台北',u'桃園',u'大陸',u'彰化',u'高雄',\
                u'台中' ,u'基隆',u'金門',u'嘉義',u'宜蘭',u'花蓮',u'花蓮',u'南投',u'苗栗',u'台南',u'雲林',\
                u'屏東',u'新竹',u'台東',u'新北',u'竹縣',u'嘉市',u'嘉縣',u'投縣',u'北京',u'竹市',u'華盛頓',u'東京'u'馬尼拉',u'北美']

         sp0 = [u'報導',u'（',u'，',u'撰稿人',u'〔',u'('] #split[0]
         sp1 = [u'記者',u'作家',u'評論員',u'編輯',u'教授',u'研究員',u'■',u'經理人',u'特派員',\
                u'特派',u'：',u'新聞網',u'協理',u'▉',u'管理師',u'醫師'] #split[-1]
         ifsp0 = [u'／',u'╱',u'/',u'∕',u':',u'_',u'｜']
         ifsp1 = [u'編譯',u'線上']
         retext = [u'時報',u'採訪',u'提供',u'整理',u'中心',u'編譯',u'撰文',u'綜合',u'出版',u'聯合報' \
                ,u'摘自',u'聯合',u'晚報',u'線上',u'新鮮事',u'本報',u'社論',u'工商部',u'資訊' \
                 ,u'車評',u'攝影',u'剪輯',u'演出',u'特稿',u'版面',u'構成',u'連線',u'國際',u'生活',u'政治' \
                 ,u'財經',u'娛樂',u'社會',u'國際',u'消費',u'Video',u'中央社',u'專訪',u'人物',u'側寫',u'界面'\
                 ,u'專題',u'一日電',u' 地方',u'鉅亨網',u'新浪',u'新聞',u'外電',u'鉅亨',u'今日',u'日报',u'數位'\
                 ,u'創業',u'小聚',u'改寫',u'時代','SNG',u'小組',u'公司',u'【',u'營養師',u'早安',u'院長',u'寵物'\
                 ,u'志工',u'講師','特別'] #replace
         IOtext = [u'文',u'圖',u'文、圖',u'圖、文',u'影音',u'影',u'口述',u'圖文']
         resub = [u'日電',u'日外電',u'專電']
         rech = {u',':u'、',u'，':u'、',u'`':'',',':u'、'}
         retime = ['%Y-%m-%d','%Y-%m-%d-%H-%M']

         while True:
            if u'\xa0' not in author:
                break
            author = author.replace(u'\xa0',' ')

         if (len(author.split(u'（')[0])==0) and (u'）' in author):
            author = re.sub('.*'+u'（'+'(\S+)'+u'）'+'.*',r'\1',author)

         if (len(author.split(u'(')[0])==0) and (u')' in author):
            author = re.sub('.*'+u'('+'(\S+)'+u')'+'.*',r'\1',author)

         for i in spname:
            author = author.split(i)[0]

         for i in rech.keys():
            author = author.replace(i,rech[i])

         for i in sp0:
            author = author.split(i)[0]
         for i in sp1:
            if i in author:
                author = author.split(i)[-1]
         for i in ifsp1:
            if i in author:
                author = author.split(i)[-1] if len(author.split(i)[0])<1 else author


         for i in retext:
            author = author.replace(i,'')

         for i in resub:
            author = author.replace(''.join(re.findall('\d+'+i+'',author)),'')
         s = ''.join(re.findall('\w+',author))

         for i in retime:
            step = False
            le = 0
            try:
                datetime.strptime('-'.join(re.findall('\d+',author)),i)
                le = len(re.findall('\d+',author))
                step = True
            except:
                pass
            if step:
                for i in range(le):
                    author = author.replace(re.findall('\d+',author)[i],'')

         for i in ifsp0:
            step = []
            if i in author:
                for sp in author.split(i):
                    if sp not in IOtext:
                        step.append(sp)
                step =[i for i in step if (i !='')&(i!=' ')]
                author = u'、'.join(step)

         en = ''.join(re.findall('\w+',author)) #將英文、數字取出

         if u'、' in author:
            author = author if len(author.split(u'、')[-1])-len(en)<=4 else u'無作者' #排除部分擁有、的內文
            for i in author.split(u'、'):
                if len(i)<=1:
                   author = i.replace(i,'')

            author = author if len(''.join(author.split(u'、')))>3 else u'無作者'

         author = ' '.join([i for i in author.split(' ') if len(i)>1])

         author = author.strip()
         author = u'、'.join([i for i in author.split(u'、') if i!=''])

         author = author if re.match('\w+',''.join(author.split(' '))) else author.split(' ')[-1]

         author = author if (len(author.replace(' ',''))-len(en))<=3 else author if u'、' in author else u'無作者' #計算非英文的字串且不包含、的，大於3則為無作者
         author = author if len(author)>1 else u'無作者' #如果值為空判定為無作者
         author = author.replace(u'　',u'、')

         return author
